﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class PhongDAO : ConnectionDAO
    {
        public static long KtMaPhong(long maPhong)
        {
            OleDbConnection cN = null;
            long temp = 0;

            try
            {
                cN = Cnect();

                string cmString = @"SELECT P.MaPhong
                                    FROM PHONG P
                                    WHERE P.MaPhong=@MaPhong";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@MaPhong", OleDbType.Integer);
                thamSo.Value = maPhong;
                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();


                while (rDer.Read())
                {
                    if (!rDer.IsDBNull(0))
                        temp = rDer.GetInt32(0);
                    else
                        temp = 0;
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            if (temp == 0)
            {
                return 0;
            }
            return temp;
        }


        public static int KtTenPhong(string tenPhong)
        {
            OleDbConnection cN = null;
            string ctemp = "";

            try
            {
                cN = Cnect();

                string cmString = @"SELECT P.TenPhong
                                    FROM PHONG P
                                    WHERE P.TenPhong=@TenPhong";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@TenPhong", OleDbType.VarChar);
                thamSo.Value = tenPhong;
                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();


                while (rDer.Read())
                {
                    if (!rDer.IsDBNull(0))
                        ctemp = rDer.GetString(0);
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            if (string.Compare(ctemp, tenPhong, true) != 0)
            {
                return 0;
            }
            return 1;
        }


        public static long KtTenPhong2(string tenPhong)
        {
            OleDbConnection cN = null;
            string ctemp = "";
            long tp = 0;

            try
            {
                cN = Cnect();

                string cmString = @"SELECT P.MaPhong,P.TenPhong
                                    FROM PHONG P
                                    WHERE P.TenPhong=@TenPhong";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@TenPhong", OleDbType.VarChar);
                thamSo.Value = tenPhong;
                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();


                while (rDer.Read())
                {
                    if (!rDer.IsDBNull(0))
                        tp = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        ctemp = rDer.GetString(1);
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }


            return tp;
        }


        public static int LapDanhMucPhong(PhongDTO phong)
        {
            int temp = 1;
            OleDbConnection cN = null;

            try
            {
                cN = Cnect();

                string cmString = @"INSERT INTO PHONG(MaPhong,TenPhong,MaLoaiPhong,TinhTrang,GhiChu) VALUES (@mP,@tP,@mLP,@tT,@gC)";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo;


                thamSo = new OleDbParameter("@mP", OleDbType.Integer);
                thamSo.Value = phong.MaPhong;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@tP", OleDbType.VarChar);
                thamSo.Value = phong.TenPhong;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@mLP", OleDbType.Integer);
                thamSo.Value = phong.MaLoaiPhong.MaLoaiPhong;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@tT", OleDbType.Boolean);
                thamSo.Value = phong.TinhTrang;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@gC", OleDbType.VarChar);
                thamSo.Value = phong.GhiChu;
                cM.Parameters.Add(thamSo);

                cM.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                temp = 0;
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return temp;
        }


        public static List<PhongDTO> TraCuuTheoTenPhong(string tenPhong)
        {
            OleDbConnection cN = null;
            List<PhongDTO> Phong = new List<PhongDTO>();
            tenPhong = "%" + tenPhong + "%";

            try
            {
                cN = Cnect();

                string cmString = @"SELECT P.MaPhong,P.TenPhong,LP.LoaiPhong,DG.DonGia,P.TinhTrang,P.GhiChu
                                    FROM PHONG P,LOAIPHONG LP,DONGIA DG
                                    WHERE P.MaLoaiPhong=LP.MaLoaiPhong AND LP.MaDonGia=DG.MaDonGia AND P.TenPhong LIKE @tP
                                    ORDER BY P.MaPhong asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@tP", OleDbType.VarChar);
                thamSo.Value = tenPhong;

                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    PhongDTO newPhong = new PhongDTO();

                    if (!rDer.IsDBNull(0))
                        newPhong.MaPhong = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        newPhong.TenPhong = rDer.GetString(1);
                    if (!rDer.IsDBNull(2))
                        newPhong.MaLoaiPhong.TenPhong = rDer.GetString(2);
                    if (!rDer.IsDBNull(3))
                        newPhong.MaLoaiPhong.MaDonGia.DonGia = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        newPhong.TinhTrang = rDer.GetBoolean(4);
                    if (!rDer.IsDBNull(5))
                        newPhong.GhiChu = rDer.GetString(5);

                    Phong.Add(newPhong);
                }
            }
            catch (Exception ex)
            {
                Phong = new List<PhongDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return Phong;
        }

        public static List<PhongDTO> TraCuuTheoLoaiPhong(long maLoaiPhong)
        {
            OleDbConnection cN = null;
            List<PhongDTO> Phong = new List<PhongDTO>();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT P.MaPhong,P.TenPhong,LP.LoaiPhong,DG.DonGia,P.TinhTrang,P.GhiChu
                                    FROM PHONG P,LOAIPHONG LP,DONGIA DG
                                    WHERE P.MaLoaiPhong=LP.MaLoaiPhong AND LP.MaDonGia=DG.MaDonGia AND P.MaLoaiPhong=@lP
                                    ORDER BY P.MaPhong asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@lP", OleDbType.Integer);
                thamSo.Value = maLoaiPhong;

                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    PhongDTO newPhong = new PhongDTO();

                    if (!rDer.IsDBNull(0))
                        newPhong.MaPhong = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        newPhong.TenPhong = rDer.GetString(1);
                    if (!rDer.IsDBNull(2))
                        newPhong.MaLoaiPhong.TenPhong = rDer.GetString(2);
                    if (!rDer.IsDBNull(3))
                        newPhong.MaLoaiPhong.MaDonGia.DonGia = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        newPhong.TinhTrang = rDer.GetBoolean(4);
                    if (!rDer.IsDBNull(5))
                        newPhong.GhiChu = rDer.GetString(5);

                    Phong.Add(newPhong);
                }
            }
            catch (Exception ex)
            {
                Phong = new List<PhongDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return Phong;
        }

        public static List<PhongDTO> TraCuuPhongTrong()
        {
            OleDbConnection cN = null;
            List<PhongDTO> Phong = new List<PhongDTO>();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT P.MaPhong,P.TenPhong,LP.LoaiPhong,DG.DonGia,P.TinhTrang,P.GhiChu
                                    FROM PHONG P,LOAIPHONG LP,DONGIA DG
                                    WHERE P.MaLoaiPhong=LP.MaLoaiPhong AND LP.MaDonGia=DG.MaDonGia AND P.TinhTrang=false
                                    ORDER BY P.MaPhong asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);



                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    PhongDTO newPhong = new PhongDTO();

                    if (!rDer.IsDBNull(0))
                        newPhong.MaPhong = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        newPhong.TenPhong = rDer.GetString(1);
                    if (!rDer.IsDBNull(2))
                        newPhong.MaLoaiPhong.TenPhong = rDer.GetString(2);
                    if (!rDer.IsDBNull(3))
                        newPhong.MaLoaiPhong.MaDonGia.DonGia = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        newPhong.TinhTrang = rDer.GetBoolean(4);
                    if (!rDer.IsDBNull(5))
                        newPhong.GhiChu = rDer.GetString(5);

                    Phong.Add(newPhong);
                }
            }
            catch (Exception ex)
            {
                Phong = new List<PhongDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return Phong;
        }

        public static List<PhongDTO> TraCuuPhongDaMuon()
        {
            OleDbConnection cN = null;
            List<PhongDTO> Phong = new List<PhongDTO>();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT P.MaPhong,P.TenPhong,LP.LoaiPhong,DG.DonGia,P.TinhTrang,P.GhiChu
                                    FROM PHONG P,LOAIPHONG LP,DONGIA DG
                                    WHERE P.MaLoaiPhong=LP.MaLoaiPhong AND LP.MaDonGia=DG.MaDonGia AND P.TinhTrang=true
                                    ORDER BY P.MaPhong asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);



                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    PhongDTO newPhong = new PhongDTO();

                    if (!rDer.IsDBNull(0))
                        newPhong.MaPhong = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        newPhong.TenPhong = rDer.GetString(1);
                    if (!rDer.IsDBNull(2))
                        newPhong.MaLoaiPhong.TenPhong = rDer.GetString(2);
                    if (!rDer.IsDBNull(3))
                        newPhong.MaLoaiPhong.MaDonGia.DonGia = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        newPhong.TinhTrang = rDer.GetBoolean(4);
                    if (!rDer.IsDBNull(5))
                        newPhong.GhiChu = rDer.GetString(5);

                    Phong.Add(newPhong);
                }
            }
            catch (Exception ex)
            {
                Phong = new List<PhongDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return Phong;
        }

        public static List<PhongDTO> TraCuuTatCa()
        {
            OleDbConnection cN = null;
            List<PhongDTO> Phong = new List<PhongDTO>();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT P.MaPhong,P.TenPhong,LP.LoaiPhong,DG.DonGia,P.TinhTrang,P.GhiChu,LP.MaLoaiPhong
                                    FROM PHONG P,LOAIPHONG LP,DONGIA DG
                                    WHERE P.MaLoaiPhong=LP.MaLoaiPhong AND LP.MaDonGia=DG.MaDonGia
                                    ORDER BY P.MaPhong asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);



                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    PhongDTO newPhong = new PhongDTO();

                    if (!rDer.IsDBNull(0))
                        newPhong.MaPhong = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        newPhong.TenPhong = rDer.GetString(1);
                    if (!rDer.IsDBNull(2))
                        newPhong.MaLoaiPhong.TenPhong = rDer.GetString(2);
                    if (!rDer.IsDBNull(3))
                        newPhong.MaLoaiPhong.MaDonGia.DonGia = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        newPhong.TinhTrang = rDer.GetBoolean(4);
                    if (!rDer.IsDBNull(5))
                        newPhong.GhiChu = rDer.GetString(5);
                    if (!rDer.IsDBNull(6))
                        newPhong.MaLoaiPhong.MaLoaiPhong = rDer.GetInt32(6);

                    Phong.Add(newPhong);
                }
            }
            catch (Exception ex)
            {
                Phong = new List<PhongDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return Phong;
        }

        public static PhongDTO LayTTPTheoMP(long mP)
        {
            OleDbConnection cN = null;
            PhongDTO Phong = new PhongDTO();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT P.MaPhong,P.TenPhong,LP.LoaiPhong,DG.DonGia,P.TinhTrang,P.GhiChu
                                    FROM PHONG P,LOAIPHONG LP,DONGIA DG
                                    WHERE P.MaLoaiPhong=LP.MaLoaiPhong AND LP.MaDonGia=DG.MaDonGia AND P.MaPhong=@mP";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@mP", OleDbType.Integer);
                thamSo.Value = mP;

                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    if (!rDer.IsDBNull(0))
                        Phong.MaPhong = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        Phong.TenPhong = rDer.GetString(1);
                    if (!rDer.IsDBNull(2))
                        Phong.MaLoaiPhong.TenPhong = rDer.GetString(2);
                    if (!rDer.IsDBNull(3))
                        Phong.MaLoaiPhong.MaDonGia.DonGia = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        Phong.TinhTrang = rDer.GetBoolean(4);
                    if (!rDer.IsDBNull(5))
                        Phong.GhiChu = rDer.GetString(5);
                }
            }
            catch (Exception ex)
            {
                Phong = new PhongDTO();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return Phong;
        }

        public static int XoaP(long mP)
        {
            int temp = 1;
            OleDbConnection cN = null;

            try
            {
                cN = Cnect();

                string cmString = @"DELETE FROM PHONG P
                                    WHERE P.MaPhong=@mP";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo;


                thamSo = new OleDbParameter("@mP", OleDbType.Integer);
                thamSo.Value = mP;
                cM.Parameters.Add(thamSo);

                cM.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                temp = 0;
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return temp;
        }

        public static long Update(PhongDTO phong)
        {
            long temp = 1;
            OleDbConnection cN = null;

            try
            {
                cN = Cnect();

                string cmString = @"UPDATE PHONG
                                    SET TenPhong=@tP,MaLoaiPhong=@mLP,TinhTrang=@tT,GhiChu=@gC
                                    WHERE MaPhong=@mP";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo;




                thamSo = new OleDbParameter("@tP", OleDbType.VarChar);
                thamSo.Value = phong.TenPhong;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@mLP", OleDbType.Integer);
                thamSo.Value = phong.MaLoaiPhong.MaLoaiPhong;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@tT", OleDbType.Boolean);
                thamSo.Value = phong.TinhTrang;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@gC", OleDbType.VarChar);
                thamSo.Value = phong.GhiChu;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@mP", OleDbType.Integer);
                thamSo.Value = phong.MaPhong;
                cM.Parameters.Add(thamSo);

                cM.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                temp = 0;
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return temp;
        }

        public static long UpdateTT(PhongDTO phong)
        {
            long temp = 1;
            OleDbConnection cN = null;

            try
            {
                cN = Cnect();

                string cmString = @"UPDATE PHONG
                                    SET TinhTrang=@tT
                                    WHERE MaPhong=@mP";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo;






                thamSo = new OleDbParameter("@tT", OleDbType.Boolean);
                thamSo.Value = phong.TinhTrang;
                cM.Parameters.Add(thamSo);


                thamSo = new OleDbParameter("@mP", OleDbType.Integer);
                thamSo.Value = phong.MaPhong;
                cM.Parameters.Add(thamSo);

                cM.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                temp = 0;
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return temp;
        }

        public static List<PhongDTO> TraCuuTheoTenKH(string tKH)
        {
            OleDbConnection cN = null;
            List<PhongDTO> Phong = new List<PhongDTO>();
            tKH = "%" + tKH + "%";

            try
            {
                cN = Cnect();

                string cmString = @"SELECT P.MaPhong,P.TenPhong,LP.LoaiPhong,DG.DonGia,P.TinhTrang,P.GhiChu
                                    FROM PHONG P,PHIEUTHUE PT,CHITIET_PT CT,KHACHHANG K,LOAIPHONG LP,DONGIA DG
                                    WHERE P.MaLoaiPhong=LP.MaLoaiPhong AND LP.MaDonGia=DG.MaDonGia AND P.MaPhong=PT.MaPhong AND PT.MaPhieuThue=CT.MaPhieuThue AND CT.MaKH=K.MaKH AND K.TenKH LIKE @tKH 
                                    ORDER BY P.MaPhong asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@tKH", OleDbType.VarChar);
                thamSo.Value = tKH;

                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    PhongDTO newPhong = new PhongDTO();

                    if (!rDer.IsDBNull(0))
                        newPhong.MaPhong = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        newPhong.TenPhong = rDer.GetString(1);
                    if (!rDer.IsDBNull(2))
                        newPhong.MaLoaiPhong.TenPhong = rDer.GetString(2);
                    if (!rDer.IsDBNull(3))
                        newPhong.MaLoaiPhong.MaDonGia.DonGia = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        newPhong.TinhTrang = rDer.GetBoolean(4);
                    if (!rDer.IsDBNull(5))
                        newPhong.GhiChu = rDer.GetString(5);

                    Phong.Add(newPhong);
                }
            }
            catch (Exception ex)
            {
                Phong = new List<PhongDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return Phong;
        }
    }
}
